package com.eastcom.collection.tools.db
import java.sql.ResultSet
import java.sql.Types;
import java.util.List;
import java.util.Map;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.AbstractApplicationContext;
import org.springframework.context.support.FileSystemXmlApplicationContext;

import com.eastcom.collection.provider.DynamicBeanRegisterProvider;
class PmDatabaseCreateUtil {
	static String sql =
	"select A.BEGINTIME,\n"+
	"A.ENDTIME,\n"+
	"A.INFOMODELREFERENCED INFOMODELREFERENCED,\n"+
	"A.DNPREFIX,\n"+
	"A.SENDERNAME,\n"+
	"A.VENDORNAME,\n"+
	"A.JOBID,\n"+
	"A.OBJECTTYPE OBJECTTYPE,\n"+
	"B.PM_NAME PM_NAME,\n"+
	"B.PM_DN,\n"+
	"B.PM_USERLABEL,\n"+
	"B.PM_VALUE\n"+
	"from nhm_pm_unit_collect_kpi_group A, nhm_pm_unit_collect_kpi_detail B\n"+
	"where A.GROUP_ID = B.GROUP_ID\n"+
	"and A.GROUP_ID= ?\n"+
	"and B.pm_dn= ?\n"+
	"order by PM_NAME desc"
	public static boolean CreateTableIfNotExsit(){

	}

	public static void main(String[] args) {
		ApplicationContext context = new FileSystemXmlApplicationContext("config/server_base/dao_config.xml")
		DbEngine dbEngine = new DbEngine()
		JdbcUtil jdbcUtil = new JdbcUtil()
		jdbcUtil.setDataSource(context.getBean("oracle_39_nhm"))
		dbEngine.setJdbcUtil(jdbcUtil)
		Object[] param = new Object[2]
		param[0] = args[0]
		param[1] = args[1]
		int[] type = new int[2]
		type[0] = Types.VARCHAR
		type[1] = Types.VARCHAR
		ResultSet rs = dbEngine.getJdbcUtil().queryPageAbsolute(sql, param, type, 1, 500)
		if(rs == null){
			println "query error!"
			return
		}
		rs.absolute(1)
		List<String> pmNameList = new ArrayList<String>()
		List<Integer> typeList = new ArrayList<String>()
		List<Integer> feildList = new ArrayList<String>()
		String pmName = rs.getString("PM_NAME")
		pmNameList.add(pmName)
		while(rs.next()){
			pmName = rs.getString("PM_NAME")
			pmNameList.add(pmName)
		}
		String tableName = "SGSN_PM_V3_SGSNFUNCTION"
		Map<String,String> dstDbFeildAndComment2 = new HashMap<String, String>()
		String selectSql0 =
		"SELECT t1.COLUMN_NAME COLUMN_NAME,\n"+
		"t2.COMMENTS COMMENTS\n"+
		"FROM user_tab_columns t1,\n"+
		"user_col_comments t2\n"+
		"WHERE t1.TABLE_NAME= ?\n"+
		"AND t1.TABLE_NAME  =t2.TABLE_NAME\n"+
		"AND t1.COLUMN_NAME =t2.COLUMN_NAME"
		Object[] params = new Object[1]
		params[0] = tableName
		int[] types = new int[1]
		types[0] = Types.VARCHAR
		try {
			List<Map<String, Object>> result = dbEngine.getJdbcUtil().queryForList(selectSql0, params, types);
			for (int i = 0; i < result.size(); i++) {
				Map<String, Object> res = result.get(i)
				String COLUMN_NAME = res.get("COLUMN_NAME")
				String COMMENTS = res.get("COMMENTS") == null ? "" : res.get("COMMENTS")
				dstDbFeildAndComment2.put(COLUMN_NAME, COMMENTS)
				println COLUMN_NAME+"--"+COMMENTS
			}
		} catch (Exception e) {
			// TODO: handle exception
		}
		rs.absolute(1)
		//		String tbTemp = rs.getString("INFOMODELREFERENCED").substring(0, 10)+ "_"+rs.getString("OBJECTTYPE")
		//		String tableName = tbTemp.replaceAll("-", "_")
		tableName = "SGSN_PM_V3_SGSNFUNCTION"
		int temp = 0
		Map<String,String> feildMap = new HashMap()
		feildList.add("INFOMODELREFERENCED")
		feildList.add("DNPREFIX")
		feildList.add("SENDERNAME")
		feildList.add("VENDORNAME")
		feildList.add("JOBID")
		feildList.add("OBJECTTYPE")
		feildList.add("PM_DN")
		feildList.add("PM_USERLABEL")
		typeList.add(Types.VARCHAR)
		typeList.add(Types.VARCHAR)
		typeList.add(Types.VARCHAR)
		typeList.add(Types.VARCHAR)
		typeList.add(Types.VARCHAR)
		typeList.add(Types.VARCHAR)
		typeList.add(Types.VARCHAR)
		typeList.add(Types.VARCHAR)
		pmNameList.each {
			println it
			//			String fd = it.replaceAll("_", "")
			String fd = it
			fd = fd.replaceAll("\\.", "_")
			if(fd.length()>30){
				fd = fd.substring(0,28)+temp++
			}
			String com = dstDbFeildAndComment2.get(fd)
			println com
			feildMap.put(fd, it.value)
			feildList.add(fd)
			typeList.add(Types.VARCHAR)
		}
		println pmNameList.size()
//		PmDatabaseCreateUtil.createDomainTable(dbEngine, feildList, typeList, tableName)
//		PmDatabaseCreateUtil.commentDomainTable(dbEngine, feildMap, tableName)
	}

	public static boolean createDomainTable(DbEngine dbEngine, List<String> feilds,List<Integer> types,String tableName){
		String sql = "create table "+tableName+" ("
		sql+="\tSTART_TIME DATE,\n"
		sql+="\tEND_TIME DATE,\n"
		for(int i = 0; i < feilds.size(); i ++){
			sql+="\t"
			sql+=feilds[i]
			sql+="\t"
			switch(types[i]){
				case Types.INTEGER:
				case Types.FLOAT:
				case Types.DOUBLE:
				case Types.DECIMAL:
				case Types.NUMERIC:
					sql+="NUMBER"
					break
				case Types.TIMESTAMP:

				case Types.DATE:
					sql+="DATE"
					break
				case Types.VARCHAR:
					sql+="VARCHAR2(512)"
				default:
					break;
			}
			if(i < feilds.size()-1){
				sql+=",\n"
			}
		}
		sql+=")"
		println sql
		dbEngine.getJdbcUtil().execute(sql)
	}

	public static boolean commentDomainTable(DbEngine dbEngine, Map<String, String> feildMap,String tableName){
		for(int i = 0; i < feildMap.keySet().size(); i ++){
			String sql = "comment on column "+tableName+"."
			sql += feildMap.keySet()[i]
			sql += " is '"
			sql += feildMap.get(feildMap.keySet()[i])
			sql +="'"
			println sql
			dbEngine.getJdbcUtil().execute(sql)
		}
	}
}
